package mypackage.dao.impl;

import mypackage.bean.Goods;
import mypackage.dao.GoodsDao;
import mypackage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author YH925
 * @create 2021/12/5 20:07
 */
public class GoodsImpl implements GoodsDao {
    Connection conn = null;

    {
        try {
            conn = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    QueryRunner runner = new QueryRunner();

    @Override
    public List<Goods> selectAllGoods() {
        String sql = "select * from Goods";
        BeanListHandler<Goods>handler = new BeanListHandler<>(Goods.class);
        List<Goods> goods = null;
        try {
            goods = runner.query(conn, sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public List<Goods> selectGoodsByBrand(String brand) {
        String sql = "select * from Goods where brand = ?";
        BeanListHandler<Goods>handler = new BeanListHandler<>(Goods.class);
        List<Goods> goods = null;
        try {
            goods = runner.query(conn, sql, handler,brand);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    //查出表中共有多少条数据
    @Override
    public int selectGoodsCount() {
        String sql = "select count(*) from goods";
        ScalarHandler<Object>handler = new ScalarHandler<>();

        Object obj = null;
        try {
            obj = runner.query(conn, sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Integer counts = Integer.parseInt(obj.toString());

        return counts;
    }

    //查出商品表中最贵的商品价格
    @Override
    public double selectGoodsMaxPrice() {
        String sql = "select max(outputmoney) from goods";
        double maxPrice = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            maxPrice = 0;
            if (resultSet.next()){
                maxPrice = resultSet.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return maxPrice;
    }

    //查出商品表中最便宜的商品价格
    @Override
    public double selectGoodsMinPrice() {
        String sql = "select min(outputmoney) from goods";
        double minPrice = 0;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            minPrice = 0;
            if (resultSet.next()){
                minPrice = resultSet.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return minPrice;
    }

    //根据价格范围查找商品
    @Override
    public List<Goods> selectGoodsByPrice(double begin, double end) {
        String sql = "select * from goods where OUTPUTMONEY >= ? and OUTPUTMONEY <= ?";
        BeanListHandler<Goods>handler = new BeanListHandler<>(Goods.class);
        List<Goods> goods = null;
        try {
            goods = runner.query(conn, sql, handler, begin, end);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public Goods selectGoodsById(String id) {
        String sql = "select * from Goods where id = ?";
        BeanHandler<Goods>handler = new BeanHandler<>(Goods.class);
        Goods good = null;
        try {
            good = runner.query(conn, sql, handler, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return good;
    }

    //按照价格升序排列
    @Override
    public List<Goods> selectGoodsASC() {
        String sql = "select * from Goods ORDER by OUTPUTMONEY asc";
        BeanListHandler<Goods>handler = new BeanListHandler<>(Goods.class);
        List<Goods> goods = null;
        try {
            goods = runner.query(conn, sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public List<Goods> selectGoodsDESC() {
        String sql = "select * from Goods ORDER by OUTPUTMONEY desc";
        BeanListHandler<Goods>handler = new BeanListHandler<>(Goods.class);
        List<Goods> goods = null;
        try {
            goods = runner.query(conn, sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    /**
     * 用户购买几件商品就要将商品数量减几件
     * 已添加事务
     * @param id
     * @param count
     */
    @Override
    public void minusOneGood(String id,int count) {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "update goods set amount = amount - ? where id = ?";
        try {
            int update = runner.update(conn, sql, count,id);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    @Override
    public void updateInfo(int choose, String goodsId,Object newInfo) {
        switch (choose){
            case 1:
                String sql = "update goods set inputmoney = ? where id = ?";
                try {
                    runner.update(conn,sql,Double.parseDouble(newInfo.toString()),goodsId);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
            case 2:
                String sql2 = "update goods set outputmoney = ? where id = ?";
                try {
                    runner.update(conn,sql2,Double.parseDouble(newInfo.toString()),goodsId);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
            case 3:
                String sql3 = "update goods set amount = ? where id = ?";
                try {
                    runner.update(conn,sql3,(Integer)newInfo,goodsId);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
            case 4:
                String sql4 = "update goods set description = ? where id = ?";
                try {
                    runner.update(conn,sql4,(String)newInfo,goodsId);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                break;
        }
    }

    @Override
    public void insert(Goods goods) {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into goods values(?,?,?,to_date(?,'YYYY-MM-DD HH24:MI:SS'),?,?,?,null)";

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm;ss");
        String time = dateFormat.format(date);

        try {
            runner.update(conn,sql,goods.getName(),goods.getInputMoney(),goods.getOutputMoney(),time,goods.getBrand(),goods.getAmount(),goods.getDescription());
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }
}
